package com.example.demo.controller;


import com.example.demo.model.ApplicationInformation;
import com.example.demo.model.StatisticsDTO;
import com.example.demo.repository.ApplicationInformationRepository;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.PageRequest;
import org.springframework.data.jpa.domain.Specification;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.util.StringUtils;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
import javax.persistence.criteria.CriteriaBuilder;
import javax.persistence.criteria.CriteriaQuery;
import javax.persistence.criteria.Predicate;
import javax.persistence.criteria.Root;
import javax.servlet.http.HttpServletRequest;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import static com.example.demo.util.LangUtil.getSafeInt;


/**
 * Created by huang on 2018/1/9.
 */
@RequestMapping("/api/v1")
@RestController
@Transactional
public class StatisticsController extends BaseController {

    @Autowired
    ApplicationInformationRepository applicationInformationRepository;

    @PersistenceContext
    private EntityManager em;

    @GetMapping("statistics")
    public ReturnDTO query(HttpServletRequest request0) {
        PageRequest pageRequest = this.buildPageRequest(request0);
        Page<ApplicationInformation> applicationInformationList;
        String province = request0.getParameter("province");
        String[] createTime = request0.getParameterValues("createTime");

        String sql = "select sum(1) as total, sum(if(accept_result='受理',1,0)) as accepted , sum(if(accept_result='不受理',1,0)) as unaccepted , sum(if(written_result='通过',1,0)) as passed , sum(if(written_result='未通过',1,0)) as unpassed , province from kgj.application_information ";
        if(createTime!=null && createTime.length > 0){
            sql += " where create_time >= '"+createTime[0]+"' and create_time <= '"+createTime[1]+"' ";
        }
        sql += " group by province ";
        if(province != null){
            sql += " having province like '%"+province+"%' ";
        }
        sql += " order by total desc ";
        int beg = pageRequest.getPageNumber()*pageRequest.getPageSize();
        sql += "limit "+beg+" ,"+pageRequest.getPageSize();
        List queryResult = em.createNativeQuery(sql).getResultList();

        List<StatisticsDTO> respResult = new ArrayList<>();
        for(Object obj : queryResult){
            Object[] obj2 = (Object[]) obj;
            respResult.add(new StatisticsDTO(getSafeInt(obj2[0]), getSafeInt(obj2[1]), getSafeInt(obj2[2]), getSafeInt(obj2[3]), getSafeInt(obj2[4]), (String)obj2[5]));
        }

        ReturnDTO returnDTO = new ReturnDTO();
        returnDTO.setData(respResult);
        returnDTO.setTotal(respResult.size());
        return returnDTO;

    }

    private Specification<ApplicationInformation> getWhereClause(final String province, final String[] createTime) {
        return new Specification<ApplicationInformation>() {
            @Override
            public Predicate toPredicate(Root<ApplicationInformation> root, CriteriaQuery<?> criteriaQuery, CriteriaBuilder criteriaBuilder) {
                Predicate predicate = criteriaBuilder.conjunction();

                if (!StringUtils.isEmpty(province)) {
                    predicate.getExpressions().add(
                            criteriaBuilder.equal(root.<String>get("province"), province));
                }
                SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
                if (!StringUtils.isEmpty(createTime)) {
                    try {
                        predicate.getExpressions().add(
                                criteriaBuilder.between(root.<Date>get("createTime"), sdf.parse(createTime[0]), sdf.parse(createTime[1]))
                        );
                    } catch (ParseException e) {
                        e.printStackTrace();
                    }
                }
                return predicate;
            }
        };
    }

}
